from impl.perf_stat.function_call import record_running_time

_create_table_sql_statement = '''
CREATE TABLE `{}` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `category_identity` VARCHAR(64) NOT NULL,
    `thumbnail_short_url` VARCHAR(128) NOT NULL,
    `search_word` TEXT NOT NULL,
    `file_name` TEXT NOT NULL,
    `url` TEXT NOT NULL,
    `source_url` TEXT,
    `source_image_url` TEXT,
    `caption` TEXT,
    `hash` CHAR(64) AS (SHA2(CONCAT(`category_identity`, `thumbnail_short_url`), 256)) STORED NOT NULL,
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `modify_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_hash` (`hash`),
    INDEX `index_identity` (`category_identity`)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
'''

_drop_table_sql_statement = 'DROP TABLE `{}`'
_exists_record_sql_statement = 'SELECT EXISTS(SELECT * FROM `{}` WHERE `hash` = (SHA2(CONCAT(%s, %s), 256)))'
_new_record_sql_statement = '''
INSERT INTO `{}` (
    `category_identity`, `thumbnail_short_url`, `search_word`, `file_name`, `url`, `source_url`, `source_image_url`, `caption`) values (%s, %s, %s, %s, %s, %s, %s, %s)'''
_count_all_sql_statement = 'SELECT COUNT(*) FROM `{}`'
_count_by_identity_sql_statement = "SELECT COUNT(*) FROM `{}` WHERE `category_identity` = %s"
_select_id_file_url_sql_statement = 'SELECT `id`, `category_identity`, `thumbnail_short_url`, `search_word`, `file_name`, `url`, `source_url`, `source_image_url`, `caption` from `{}`'


class BaiduCrawlerRecordDAO:
    def __init__(self, db_connection):
        self.db_connection = db_connection
        table_name = self.db_connection.table_names['record_table_name']
        self._create_table_sql_statement = _create_table_sql_statement.format(table_name)
        self._drop_table_sql_statement = _drop_table_sql_statement.format(table_name)
        self._exists_record_sql_statement = _exists_record_sql_statement.format(table_name)
        self._new_record_sql_statement = _new_record_sql_statement.format(table_name)
        self._count_all_sql_statement = _count_all_sql_statement.format(table_name)
        self._count_by_identity_sql_statement = _count_by_identity_sql_statement.format(table_name)
        self._select_id_file_url_sql_statement = _select_id_file_url_sql_statement.format(table_name)

    @record_running_time
    def exists(self, identity: str, thumbnail_short_url: str):
        self.db_connection.cursor.execute(self._exists_record_sql_statement, (identity, thumbnail_short_url))
        result = self.db_connection.cursor.fetchall()
        assert len(result) == 1
        return result[0][0] == 1

    @record_running_time
    def insert(self, identity: str, thumbnail_short_url: str, search_word: str, file_name: str, url: str, source_url: str, source_image_url: str, caption: str):
        self.db_connection.cursor.execute(self._new_record_sql_statement, (identity, thumbnail_short_url, search_word, file_name, url, source_url, source_image_url, caption))

    @record_running_time
    def insert_multiple(self, records):
        self.db_connection.cursor.executemany(self._new_record_sql_statement, records)

    def create_table(self):
        self.db_connection.cursor.execute(self._create_table_sql_statement)

    def drop_table(self):
        self.db_connection.cursor.execute(self._drop_table_sql_statement)

    @record_running_time
    def count_all(self):
        self.db_connection.cursor.execute(self._count_all_sql_statement)
        return self.db_connection.cursor.fetchone()[0]

    @record_running_time
    def count_by_identity(self, identity):
        self.db_connection.cursor.execute(self._count_by_identity_sql_statement, (identity,))
        result = self.db_connection.cursor.fetchall()
        assert len(result) == 1
        return result[0][0]

    def get_iterator(self):
        self.db_connection.cursor.execute(self._select_id_file_url_sql_statement)
        return self.db_connection.cursor

    def get_iterator_with_id_limits(self, id_min: int=None, id_max: int=None):
        if id_min is None and id_max is None:
            self.db_connection.cursor.execute(self._select_id_file_url_sql_statement)
        elif id_min is not None and id_max is not None:
            self.db_connection.cursor.execute(self._select_id_file_url_sql_statement + ' WHERE `id` >= %s AND `id` <= %s', (id_min, id_max))
        elif id_min is not None:
            self.db_connection.cursor.execute(self._select_id_file_url_sql_statement + ' WHERE `id` >= %s', (id_min,))
        elif id_max is not None:
            self.db_connection.cursor.execute(self._select_id_file_url_sql_statement + ' WHERE `id` <= %s', (id_max,))
        else:
            raise Exception
        return self.db_connection.cursor
